<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%>

<%@include file="top.jsp"%>
<%@include file="left.jsp"%>
<%	
	Connection dbcCata = db.getConnection();
	Statement stmtCata = dbcCata.createStatement();
	String sqlCata = "SELECT * FROM catelog";
	ResultSet rsCata = stmtCata.executeQuery(sqlCata);

    Connection dbcArea = db.getConnection();
	Statement stmtArea = dbcArea.createStatement();
	String sqlArea = "SELECT * FROM area";
	ResultSet rsArea = stmtArea.executeQuery(sqlArea);
    
    Integer areaId=0;
    Integer catalogId=0;
    Connection dbcNews1 = db.getConnection();
	String sqlNews1 = "SELECT areaid,catalogid FROM news";
	PreparedStatement stmtNews1 = dbcNews1.prepareStatement(sqlNews1);
	ResultSet rsNews1 = stmtNews1.executeQuery();
	if(rsNews1.next()){
	 areaId= rsNews1.getInt("areaid");
     catalogId = rsNews1.getInt("catalogid"); 
	
%> 

    <div id="content" class="align-right">
				<div id="content-ads">
					<img src="images/ads_2.png" width="800" height="90" />
				</div>
				<h2>城市情报</h2>
				<p>
				<form action="so_city_info.jsp" method="post">
				  <label>地区或类别</label>&nbsp;&nbsp;
				  <select name="catalogid" class="small-input">
				  <% while(rsCata.next()){ %>
					<option 
				  <%
					if(rsCata.getInt("catalogid") == catalogId){
					out.print(" selected='selected'");}
				  %>
					value="<%=rsCata.getInt("catalogid")%>"><%=rsCata.getString("catalogname")%></option>
				 <% } %>
				  </select>
				  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
				  <select name="areaid" class="small-input">
						<% while(rsArea.next()){ %>
						<option
						<%
							if(rsArea.getInt("areaid") == areaId){
							out.print(" selected='selected'");}
						%>

							 value="<%=rsArea.getInt("areaid")%>"><%=rsArea.getString("areaname")%></option>
						<% } %>
				</select> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
				<input class="button" type="submit" value="搜索"/>
				 </form>
				 <%}
				    rsArea.close();
					rsCata.close();
					rsNews1.close();
					dbcArea.close();
					dbcCata.close();
					dbcNews1.close();
				%> 
			    </p>
				<div id="main-content">
					<table>
							<thead>
								<tr>
								   <th style="width:20%">标题</th>
								   <th>分类</th>
								   <th style="width:35%">内容</th>
								   <th>作者</th>
								   <th>地区</th>
								   <th>发表时间</th>
								</tr>
								
							</thead>
						 						 
							<tbody>
		<%
			int pageNum = 1;	//当前第1页
			int pageSize = 10; //每页15条记录
			int recordCount;   //数据库的中的条数
			int pageCount;
			String strPageNum = request.getParameter("pagenum");
			if(null == strPageNum || "".equals(strPageNum)){
				
			}else{
				pageNum = Integer.parseInt(strPageNum);
			}
			
			Connection dbn = db.getConnection();
			//Statement stmt = dbc.createStatement();
			String sqln = "SELECT n.newsid,n.title,n.content1, n.author, n.createdate, a.areaname, c.catalogname FROM news AS n, area AS a, catelog AS c WHERE n.areaid=a.areaid AND n.catalogid=c.catalogid";
			PreparedStatement pstmt = dbn.prepareStatement(sqln, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ResultSet rsn = pstmt.executeQuery();
			rsn.last();
			recordCount = rsn.getRow();
			
			if(recordCount % pageSize == 0){
				pageCount = recordCount / pageSize;
			}else{
				pageCount = recordCount / pageSize + 1;
			}
			rsn.absolute((pageNum-1)*pageSize);
			int i = 0;
			while(rsn.next() && i < pageSize){	
	   %>
								<tr class="alt-row">
									<td><a title="title" href="event.jsp?newsid=<%=rsn.getInt("newsid")%>"><%=rsn.getString("title") %></a></td>
									<td><%=rsn.getString("catalogname") %></td>
									<td><%=rsn.getString("content1") %></td>
									<td><%=rsn.getString("author") %></td>
									<td><%=rsn.getString("areaname") %></td>
									<td><%=rsn.getString("createdate") %></td>
								</tr>
			<%
				i++;
				}
                rsn.close();
				dbn.close();
			%>
							</tbody>
													
														<tfoot>
								<tr>
									<td colspan="6">
										<div class="pagination">
											<%if(pageNum != 1){ %>
											<a href="city_info.jsp" title="首页">&laquo; 首页</a>
											<a href="city_info.jsp?pagenum=<%= pageNum-1 %>" title="前一页">&laquo; 前一页</a>
											<%} %>
											当前<%= pageNum%>页/共<%= pageCount%>页
											<%if(pageNum != pageCount){ %>
											<a href="city_info.jsp?pagenum=<%= pageNum+1 %>" title="后一页">后一页 &raquo;</a>
											<a href="city_info.jsp?pagenum=<%= pageCount %>" title="末页">末页 &raquo;</a>
											<%} %>
										</div> <!-- End .pagination -->
										<div class="clear"></div>
									</td>
								</tr>
							</tfoot>
						</table>
					</div>
			</div>



<!-- 特殊 选中样式 -->
<script type="text/javascript">
	
	$(document).ready(function(){
		
		/*选中，如下*/
		$('#li-info').addClass('on');
		
	});
</script>

<%@include file="bottom.jsp"%>
